#!/usr/bin/python3
# -*- coding:utf-8 -*-
'''
Created on 2017年3月15日
@author: yuejing
'''
import cx_Oracle
from xlrd import open_workbook
from xlutils.copy import copy
import xlrd
import xlwt

def read_sql(file_name) : #读取txt文本
    f = open("./"+file_name+".txt", "r")
    str = f.read()
    f.close()       # 关闭打开的文件
    return str

def Query(txt='list', nStart=0, nNum=-1): #获取数据库行数据；nStart开始位置，nNum行条数,nNum=-1获取至结尾
    sql=read_sql(txt)
    rt = []
    con = cx_Oracle.connect('baic2017_dm/baic2017_dm228@192.168.1.37/DBFM')
    cur = con.cursor()    # 获取cursor
    if not cur:
        return rt
    # 查询到列表
    cur.execute(sql)
    if (nStart == 0) and (nNum == 1):
        rt.append(cur.fetchone())
    else:
        rs = cur.fetchall()
        if nNum == - 1:
            rt.extend(rs[nStart:])
        else:
            rt.extend(rs[nStart:nStart + nNum])
    #print("Total: " + str(cur.rowcount)+'行数据')
    con.close()     # 释放cursor
    resul=lists(rt) # 转换成列表
    return resul

def lists(tup) : #多维元组转换成多维列表
    b = list(tup)
    for c in b:
        b[b.index(c)] = list(c)
    return b

def style():
    font = xlwt.Font()  # Create the Font
    font.name = 'Times New Roman'
    font.bold = True
    font.underline = True
    font.italic = True

    borders = xlwt.Borders()  # Create Borders
    borders.left = xlwt.Borders.DASHED      # May be: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, MEDIUM_DASH_DOTTED,                                          
    borders.right = xlwt.Borders.DASHED     #         THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
    borders.top = xlwt.Borders.DASHED
    borders.bottom = xlwt.Borders.DASHED
    borders.left_colour = 0x40
    borders.right_colour = 0x40
    borders.top_colour = 0x40
    borders.bottom_colour = 0x40

    alignment = xlwt.Alignment()  # Create Alignment
    alignment.horz = xlwt.Alignment.HORZ_CENTER  # May be: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, 
    alignment.vert = xlwt.Alignment.VERT_CENTER  # May be: HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED,VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED

    pattern = xlwt.Pattern()  # Create the Pattern
    pattern.pattern = xlwt.Pattern.SOLID_PATTERN  # May be: NO_PATTERN, SOLID_PATTERN, or 0x00 through 0x12
    pattern.pattern_fore_colour = 22 # May be: 8 through 63. 0 = Black, 1 = White, 2 = Red, 3 = Green, 4 = Blue, 5 = Yellow, 6 = Magenta, 7 = Cyan, 16 = Maroon, 17 = Dark Green,
                                     #        18 = Dark Blue, 19 = Dark Yellow , almost brown), 20 = Dark Magenta, 21 = Teal, 22 = Light Gray, 23 = Dark Gray
    style1 = xlwt.XFStyle()    # Create the Style
    style1.font = font         # Apply the Font to the Style
    style1.borders=borders     # Apply borders to Style
    style1.alignment=alignment # Apply alignment to Style
    style1.pattern=pattern     # Apply pattern to Style

    style2 = xlwt.XFStyle()    # Create the Style
    style2.borders=borders     # Apply borders to Style
    style2.pattern=pattern     # Apply pattern to Style
    return style1,style2

def excel_write(list,nwrite=0,path_file='alter.xls'): #没有默认值的参数需放前面；nwrite=0为首次写入，nwrite代表第几次写入到sheel(nwrite+1)中
    title = ['大类','小类','父配置','子配置','是否推荐','配置属性','DATA_TYPE','P_CONFIG_ID','CONFIG_ID','本品','PV','需求度',
             '成本','父装备率','子装备率','父装车率','子装车率','系统推荐','配置排序','水平配置排序','推荐排序']
    #第一次写入excel
    if nwrite==0:
        wt = xlwt.Workbook()
        Sheet1 = wt.add_sheet('Sheet1', cell_overwrite_ok=True) #第一次写入用
        Sheet2 = wt.add_sheet('Sheet2', cell_overwrite_ok=True) #第二次写入用
        Sheet3 = wt.add_sheet('Sheet3', cell_overwrite_ok=True) #第三次写入用
        Sheet4 = wt.add_sheet('Sheet4', cell_overwrite_ok=True) #第四次写入用
        Sheet5 = wt.add_sheet('Sheet5', cell_overwrite_ok=True) #第五次写入用
        #写入标题栏
        for t in range(len(title)):
            Sheet1.write(0, t, title[t],style()[0])
        # 写入列表数据
        for i in range(1,len(list)+1):
            for j in range(len(list[i-1])):
                if list[i-1][9]!=list[i-1][17]:
                    Sheet1.write(i, j, list[i-1][j],style()[1])
                else:
                    Sheet1.write(i, j, list[i-1][j])
        wt.save(path_file)
    #非第一次写入excel
    elif nwrite>=0:
        try:
            rb = open_workbook(path_file)  # 打开excel
        except:
            print('路径不存在该文件！')
        # 复制之前的sheel
        wb = copy(rb)
        # 通过get_sheet()获取的sheet有write()方法,写入表格
        try:
            ws = wb.get_sheet(nwrite)
        except:
            print('该文件不存在新的sheet可写！')
        # 写入标题栏
        title = ['大类', '小类', '父配置', '子配置', '是否推荐', '配置属性', 'DATA_TYPE', 'P_CONFIG_ID', 'CONFIG_ID', '本品', 'PV', '需求度',
                 '成本', '父装备率', '子装备率', '父装车率', '子装车率', '系统推荐', '配置排序','水平配置排序','推荐排序']
        for t in range(len(title)):
            ws.write(0, t, title[t], style()[0])
        # 写入列表数据
        for i in range(1, len(list) + 1):
            for j in range(len(list[i - 1])):
                if list[i-1][9]!=list[i-1][17]:
                    ws.write(i, j, list[i-1][j],style()[1])
                else:
                    ws.write(i, j, list[i-1][j])
        wb.save(path_file)
    else:
        print('list未写入excel！')

def excel_read(nread=0,path_file='alter.xls'):
    try:
        xlbook=xlrd.open_workbook(path_file)  #打开excel
    except:
        print('路径不存在该文件！')

    count=len(xlbook.sheets())            #获取excel工作簿数
    table = xlbook.sheet_by_index(nread)  # 通过索引获取工作表
    nrows = table.nrows  # 获取行数
    ncols = table.ncols  # 获取列数
    #print("工作簿数：%d 当前工作表行数:%d 当前工作表列数:%d" % (count, nrows, ncols))
    # 按行循环读取数据，保存到列表
    list = []
    for i in range(1, nrows):  # 从索引1开始，去除第一行
        list.append(table.row_values(i))
    return list

def base_pv_co(base='base'):        #计算本品各模块PV和成本
    ba=Query(base)
    pv_co=[[0,0,0,0,0],[0,0,0,0,0]] #本品PV_成本值[动力系统,车身底盘,安全,内部,外部]
    for br in range(len(ba)):
        if ba[br][0]=='动力系统':
            pv_co[0][0]=ba[br][1]
            pv_co[1][0]=ba[br][2]
        if ba[br][0]=='车身底盘':
            pv_co[0][1]=ba[br][1]
            pv_co[1][1]=ba[br][2]
        if ba[br][0]=='安全':
            pv_co[0][2]=ba[br][1]
            pv_co[1][2]=ba[br][2]
        if ba[br][0]=='内部':
            pv_co[0][3]=ba[br][1]
            pv_co[1][3]=ba[br][2]
        if ba[br][0]=='外部':
            pv_co[0][4]=ba[br][1]
            pv_co[1][4]=ba[br][2]
    return pv_co

def Handle_pv_co(rs):               #计算已推荐各模块PV和成本
    pv_co=[[0,0,0,0,0],[0,0,0,0,0]] #本品PV_成本值[动力系统,车身底盘,安全,内部,外部]
    for k in range(len(rs)):
        if rs[k][6]=='I':
            if rs[k][0] == '动力系统':
                pv_co[0][0]+=rs[k][10]
                pv_co[1][0]+=rs[k][12]
            elif rs[k][0] == '车身底盘':
                pv_co[0][1]+=rs[k][10]
                pv_co[1][1]+=rs[k][12]
            elif rs[k][0] == '安全':
                pv_co[0][2]+=rs[k][10]
                pv_co[1][2]+=rs[k][12]
            elif rs[k][0] == '内部':
                pv_co[0][3]+=rs[k][10]
                pv_co[1][3]+=rs[k][12]
            elif rs[k][0] == '外部':
                pv_co[0][4]+=rs[k][10]
                pv_co[1][4]+=rs[k][12]
        elif rs[k][6]=='N':
            if rs[k][0] == '动力系统':
                pv_co[0][0]+=rs[k][10]
                pv_co[1][0]+=rs[k][12]
            elif rs[k][0] == '车身底盘':
                pv_co[0][1]+=rs[k][10]
                pv_co[1][1]+=rs[k][12]
            elif rs[k][0] == '安全':
                pv_co[0][2]+=rs[k][10]
                pv_co[1][2]+=rs[k][12]
            elif rs[k][0] == '内部':
                pv_co[0][3]+=rs[k][10]
                pv_co[1][3]+=rs[k][12]
            elif rs[k][0] == '外部':
                pv_co[0][4]+=rs[k][10]
                pv_co[1][4]+=rs[k][12]
        elif rs[k][6]=='B' and rs[k][17]== '●':
            if rs[k][0] == '动力系统':
                pv_co[0][0]+=rs[k][10]
                pv_co[1][0]+=rs[k][12]
            elif rs[k][0] == '车身底盘':
                pv_co[0][1]+=rs[k][10]
                pv_co[1][1]+=rs[k][12]
            elif rs[k][0] == '安全':
                pv_co[0][2]+=rs[k][10]
                pv_co[1][2]+=rs[k][12]
            elif rs[k][0] == '内部':
                pv_co[0][3]+=rs[k][10]
                pv_co[1][3]+=rs[k][12]
            elif rs[k][0] == '外部':
                pv_co[0][4]+=rs[k][10]
                pv_co[1][4]+=rs[k][12]
        elif rs[k][6]=='T' and rs[k][17]== '●':
            if rs[k][0] == '动力系统':
                pv_co[0][0]+=rs[k][10]
                pv_co[1][0]+=rs[k][12]
            elif rs[k][0] == '车身底盘':
                pv_co[0][1]+=rs[k][10]
                pv_co[1][1]+=rs[k][12]
            elif rs[k][0] == '安全':
                pv_co[0][2]+=rs[k][10]
                pv_co[1][2]+=rs[k][12]
            elif rs[k][0] == '内部':
                pv_co[0][3]+=rs[k][10]
                pv_co[1][3]+=rs[k][12]
            elif rs[k][0] == '外部':
                pv_co[0][4]+=rs[k][10]
                pv_co[1][4]+=rs[k][12]
    return pv_co

def Modules(base,Handle,mode=0): #base本品,Handle已推荐;模块mode=0,1,2,3,4:整体,底盘微调,安全微调,内部微调,外部微调
    print('输入的TP',tp)
    print('输入的产品竞争力',cop)
    print('本品TP',btp)
    if mode==0:
        all=cop[0]*tp-btp+base[0][0]+base[0][1]+base[0][2]+base[0][3]+base[0][4]
        chassis = (all - base[0][0]) * base[0][1]/(base[0][1]+base[0][2]+base[0][3]+base[0][4])-Handle[0][1]
        safety =  (all - base[0][0]) * base[0][2]/(base[0][1]+base[0][2]+base[0][3]+base[0][4])-Handle[0][2]
        inside =  (all - base[0][0]) * base[0][3]/(base[0][1]+base[0][2]+base[0][3]+base[0][4])-Handle[0][3]
        exterior= (all - base[0][0]) * base[0][4]/(base[0][1]+base[0][2]+base[0][3]+base[0][4])-Handle[0][4]
        print('整体目标PV：',all)
        print('模块目标PV:动力%0.1f,底盘%0.1f,安全%0.1f,内部%0.1f,外部%0.1f'%(base[0][0],chassis+Handle[0][1],safety+Handle[0][2],inside+Handle[0][3],exterior+Handle[0][4]))
        print('模块剩余PV:动力%0.1f,底盘%0.1f,安全%0.1f,内部%0.1f,外部%0.1f'%(base[0][0],chassis,safety,inside,exterior))
    elif mode==1:
        chassis = (cop[2]-1)*tp+base[0][1]-Handle[0][1]
        safety,inside,exterior=0,0,0
        print('模块目标PV:底盘',round(chassis+Handle[0][1],0))
        print('模块剩余PV:底盘',round(chassis,0))
    elif mode==2: 
        safety =  (cop[3]-1)*tp+base[0][2]-Handle[0][2]
        chassis,inside,exterior=0,0,0
        print('模块目标PV:安全',round(safety+Handle[0][2],0))
        print('模块剩余PV:安全',round(safety,0))
    elif mode==3: 
        inside =  (cop[4]-1)*tp+base[0][3]-Handle[0][3]
        chassis,safety,exterior=0,0,0
        print('模块目标PV:内部',round(inside+Handle[0][3],0))
        print('模块剩余PV:内部',round(inside,0))
    elif mode==4: 
        exterior= (cop[5]-1)*tp+base[0][4]-Handle[0][4]
        chassis,safety,inside=0,0,0
        print('模块目标PV:外部',round(exterior+Handle[0][4],0))
        print('模块剩余PV:外部',round(exterior,0))
    else:
        print('Please input the right mode!')
    return  round(chassis,1),round(safety,1),round(inside,1),round(exterior,1)

def congfig(rs,chassis,safety,inside,exterior,mode=0): #RS为推荐前list;mode=0首次推荐,mode=1微调
    pp=set([])
    for j in range(len(rs)):
        if rs[j][4]=='是' and rs[j][17] == '●':
            pp.add(rs[j][8])
            print('已装:',rs[j][8])
    if mode==0: #首次推荐前三步必推，不管剩余是否为负
        for i in range(len(rs)):
            if rs[i][0] == '车身底盘' and rs[i][4]=='是' and rs[i][17] != '●' and rs[i][19] == 1 and chassis - rs[i][10] >= 0:
                chassis -= rs[i][10]
                rs[i][17] = '●'
                pp.add(rs[i][8])
            elif rs[i][0] == '安全' and rs[i][4]=='是' and rs[i][17] != '●' and rs[i][19] == 1 and safety - rs[i][10] >= 0:
                safety -= rs[i][10]
                rs[i][17] = '●'
                pp.add(rs[i][8])
            elif rs[i][0] == '内部' and rs[i][4]=='是' and rs[i][17] != '●' and rs[i][19] == 1 and inside - rs[i][10] >= 0:
                inside -= rs[i][10]
                rs[i][17] = '●'
                pp.add(rs[i][8])
            elif rs[i][0] == '外部' and rs[i][4]=='是' and rs[i][17]  != '●' and rs[i][19] == 1 and exterior - rs[i][10] >= 0:
                exterior -= rs[i][10]
                rs[i][17] = '●'
                pp.add(rs[i][8])

    if mode==1: #整体微调、分模块微调
        #推荐底盘配置
        if chassis>0:
            for i in range(len(rs)):
                if rs[i][0] == '车身底盘' and rs[i][4]=='是' and rs[i][17] != '●' and rs[i][19] == 1 and chassis - rs[i][10] >= 0:
                    chassis -= rs[i][10]
                    rs[i][17] = '●'
                    pp.add(rs[i][8])
        elif chassis<0: 
            rs.reverse()
            for i in range(len(rs)):
                if rs[i][0] == '车身底盘' and rs[i][4]=='是' and rs[i][17]!='必备水平配置' and rs[i][17] == '●':
                    if chassis + rs[i][10]<0:
                        chassis += rs[i][10]
                        rs[i][17] = '-'
                        pp.remove(rs[i][8])
                        pp.add(-rs[i][8])
                    elif chassis + rs[i][10]>=0:
                        rs[i][17] = '-'
                        pp.remove(rs[i][8])
                        pp.add(-rs[i][8])
                        break
            rs.sort(key=lambda x:x[20])
        #推荐安全配置
        if safety>0:
            for i in range(len(rs)):
                if rs[i][0] == '安全' and rs[i][4]=='是' and rs[i][17] != '●' and rs[i][19] == 1 and safety - rs[i][10] >= 0:
                    safety -= rs[i][10]
                    rs[i][17] = '●'
                    pp.add(rs[i][8])
        elif safety<0: 
            rs.reverse()
            for i in range(len(rs)):
                if rs[i][0] == '安全' and rs[i][4]=='是' and rs[i][17]!='必备水平配置' and rs[i][17] == '●':
                    if safety + rs[i][10]<0:
                        safety += rs[i][10]
                        rs[i][17] = '-'
                        pp.remove(rs[i][8])
                        pp.add(-rs[i][8])
                    elif safety + rs[i][10]>=0:
                        rs[i][17] = '-'
                        pp.remove(rs[i][8])
                        pp.add(-rs[i][8])
                        break
            rs.sort(key=lambda x:x[20])
        #推荐内部配置
        if inside>0:
            for i in range(len(rs)):
                if rs[i][0] == '内部' and rs[i][4]=='是' and rs[i][17] != '●' and rs[i][19] == 1 and inside - rs[i][10] >= 0:
                    inside -= rs[i][10]
                    rs[i][17] = '●'
                    pp.add(rs[i][8])
        elif inside<0: 
            rs.reverse()
            for i in range(len(rs)):
                if rs[i][0] == '内部' and rs[i][4]=='是' and rs[i][17]!='必备水平配置' and rs[i][17] == '●':
                    if inside + rs[i][10]<0:
                        inside += rs[i][10]
                        rs[i][17] = '-'
                        pp.remove(rs[i][8])
                        pp.add(-rs[i][8])
                    elif inside + rs[i][10]>=0:
                        rs[i][17] = '-'
                        pp.remove(rs[i][8])
                        pp.add(-rs[i][8])
                        break
            rs.sort(key=lambda x:x[20])
        #推荐外部配置
        if exterior>0:
            for i in range(len(rs)):
                if rs[i][0] == '外部' and rs[i][4]=='是' and rs[i][17]  != '●' and rs[i][19] == 1 and exterior - rs[i][10] >= 0:
                    exterior -= rs[i][10]
                    rs[i][17] = '●'
                    pp.add(rs[i][8])
        elif exterior<0: 
            rs.reverse()
            for i in range(len(rs)):
                if rs[i][0] == '外部' and rs[i][4]=='是' and rs[i][17]!='必备水平配置' and rs[i][17] == '●':
                    if exterior + rs[i][10]<0:
                        exterior += rs[i][10]
                        rs[i][17] = '-'
                        pp.remove(rs[i][8])
                        pp.add(-rs[i][8])
                    elif exterior + rs[i][10]>=0:
                        rs[i][17] = '-'
                        pp.remove(rs[i][8])
                        pp.add(-rs[i][8])
                        break
            rs.sort(key=lambda x:x[20])
    #打印推荐的ID
    for n in pp:
        print('推荐：',n)
    return rs

    
def print_config(base,new):
    print('产品竞争力:',round(100*(btp+sum(new[0])-sum(base[0]))/tp,1))
    print('动力系统:%0.1f 车身底盘:%0.1f 安全:%0.1f 内部:%0.1f 外部:%0.1f'%( 100*(tp+new[0][0]-base[0][0])/tp,100*(tp+new[0][1]-base[0][1])/tp,100*(tp+new[0][2]-base[0][2])/tp,100*(tp+new[0][3]-base[0][3])/tp,100*(tp+new[0][4]-base[0][4])/tp))
    print('推荐后PV：%d 成本:%d 溢价比:%0.1f'%(sum(new[0]),sum(new[1]),100*sum(new[0])/sum(new[1])))
    print('销量份额:',round(100*(0.5*((btp+sum(base[0])-sum(new[0]))/tp)-0.35),1))


tp=55000                 #输入TP
cop=[1,1,0.95,0.85,1,1] #输入产品竞争力[整体、动力、底盘、安全、内部、外部]
btp=60781                #本品TP
base=base_pv_co()
'''
#首次推荐
rs=Query()
excel_write(rs,0)#写入第(nwrite+1)个sheet
rt=Handle_pv_co(rs)
mod=Modules(base,rt)
tuijian=congfig(rs,mod[0],mod[1],mod[2],mod[3])
tuijian_pv_co=Handle_pv_co(tuijian)
print_config(base,tuijian_pv_co)
excel_write(tuijian,1)#写入第(nwrite+1)个sheet

#整体微调
rs=excel_read(0)#读取第(nread+1)个sheet
rt=Handle_pv_co(rs)
mod=Modules(base,rt)
tuijian=congfig(rs,mod[0],mod[1],mod[2],mod[3],1)#mode=0首次推荐,mode=1微调
tuijian_pv_co=Handle_pv_co(tuijian)
print_config(base,tuijian_pv_co)
excel_write(tuijian,1)#写入第(nwrite+1)个sheet
'''
#分模块微调
rs=excel_read(1)   #读取(nread+1)个sheet
rt=Handle_pv_co(rs)
mod=Modules(base,rt,1)  #模块mode=0,1,2,3,4:整体,底盘微调,安全微调,内部微调,外部微调
tuijian=congfig(rs,mod[0],mod[1],mod[2],mod[3],1)#mode=0首次推荐,mode=1微调
tuijian_pv_co=Handle_pv_co(tuijian)
print_config(base,tuijian_pv_co)
excel_write(tuijian,2)#写入第(nwrite+1)个sheet



